package com.studorm.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.regex.Pattern;

import com.opensymphony.xwork2.ActionSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.studorm.Connection_mysql;
import com.studorm.vo.dorm;;


public class StudentDormAction extends ActionSupport {
	
	/**
	 * 
	 */
	//private static final long serialVersionUID = 1L;
	/**
	 * 
	 */
	private static final long serialVersionUID = -3697921271744988026L;
	// 此变量为存放你要读取的表格数据，前台请求后会自动生成JSON格式发送到客户端
	private List<dorm> tableRow = new ArrayList<dorm>();
	private int amountOfRow = 0;
	public List<dorm> getTableRow() {
		return tableRow;
	}
	public void setTableRow(List<dorm> tableRow) {
		this.tableRow = tableRow;
	}

	public String loadDorms() throws Exception{
		//读取请求的参数 
		
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null ){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps;
		ResultSet res;
		String sql;

		
		/* ------------------------
		 * 	此处得到总数据条数，用来计算页数
		 * ------------------------
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT COUNT(*) FROM studormcms.dorminf WHERE dormBdName=? ";
			ps = conn.prepareStatement(sql);

		}else{
			sql = "SELECT COUNT(*) FROM studormcms.dorminf WHERE dormBdName=? AND dormNu=?";
			ps = conn.prepareStatement(sql);
			ps.setString(2, dormNu);
		}
		ps.setString(1, dormBdName);
		
		res = ps.executeQuery();
		while(res.next()){
			amountOfRow = res.getInt(1);
		}
		res.close();
		ps.close();
		if(dormNu.equals("全部")){
			sql = "SELECT id, dormTelephone,maxNuOfPp,nuOfPp,dormTelephone, dormBdName, dormNu FROM studormcms.dorminf WHERE dormBdName=?  LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(2, (nuOfP-1) * rowEachP);
			ps.setInt(3, rowEachP);
		}else{
			sql = "SELECT id,  dormTelephone,maxNuOfPp,nuOfPp,dormTelephone, dormBdName, dormNu WHERE dormBdName=?  AND dormNu=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setString(2, dormNu);
			ps.setInt(3, (nuOfP-1) * rowEachP);
			ps.setInt(4, rowEachP);
		}
		ps.setString(1, dormBdName);
		

		
		res = ps.executeQuery();
		while(res.next()){
			dorm u = new dorm();
			u.setId(res.getInt("id"));

		
			u.setDormNu(res.getString("DormNu"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setMaxNuOfPp(res.getInt("maxNuOfPp"));
			u.setNuOfPp(res.getInt("nuOfPp"));
			u.setDormTelephone(res.getString("DormTelephone"));	
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
	public String loadADorm() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String DormId = request.getParameter("DormId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(DormId == null || DormId.isEmpty() || !patternNu.matcher(DormId).matches()){
			return NONE;
		}
		String sql = "SELECT id, maxNuOfPp,nuOfPp,dormTelephone,dormBdName, dormNu FROM dorminf WHERE id = ?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(DormId));
		ResultSet res = ps.executeQuery();	
		while(res.next()){
			dorm u = new dorm();
			u.setId(res.getInt("id"));
			u.setMaxNuOfPp(res.getInt("maxNuOfPp"));
			
			
	
			u.setNuOfPp(res.getInt("nuOfPp"));
			u.setDormTelephone(res.getString("dormTelephone"));
	
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
//	
			tableRow.add(u);			
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	public String delDorm() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String uId = request.getParameter("delId");
		int id = 0;
		if(uId != null){
			id = Integer.parseInt(uId); // 这里不用转换
		}else{
			return NONE;
		}
		Connection conn = Connection_mysql.getConnection();
		String sql = "DELETE FROM studormcms.dorminf WHERE id=?"; //studentNu是char类型
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id); // 这个Int修成String
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res == 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}

	public String editDorm() throws Exception{
		
		//Calendar rightNow = Calendar.getInstance();
		//SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		//String submitDate = fmt.format(rightNow.getTime());
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
//	String  dormBdName= request.getParameter("dormBdName");
//	String dormNu = request.getParameter("dormNu");	
	//	String sex = request.getParameter("sex");
		String maxNuOfPp = request.getParameter("maxNuOfPp");
	//	String studentId = request.getParameter("studentId");
		String nuOfPp = request.getParameter("nuOfPp");	
		String dormTelephone = request.getParameter("dormTelephone");
	//	String grade = request.getParameter("grade");
	//	String department = request.getParameter("department");
	//	String major = request.getParameter("major");
	//	String id = request.getParameter("id");
        
		Pattern patternNu = Pattern.compile("\\d*");
//		patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*");
		
//		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
//			return NONE;
//		}
		if(maxNuOfPp == null || maxNuOfPp.isEmpty() || !patternNu.matcher(maxNuOfPp).matches()){
			return NONE;
		}
		if(nuOfPp == null || nuOfPp.isEmpty() ){
			return NONE;
		}
//		if(sex == null || sex.isEmpty() ){
//			return NONE;
//		}
//		if(grade == null || grade.isEmpty() ){
//			return NONE;
//		}
//		if(department == null || department.isEmpty() ){
//			return NONE;
//		}
//	if(major == null || major.isEmpty()){
//			return NONE;
//		}
//	if(dormNu == null || dormNu.isEmpty()){
//		return NONE;
//	}
	if(dormTelephone == null || dormTelephone.isEmpty()){
		return NONE;
	}
//	if(dormBdName == null || dormBdName.isEmpty() ){
//		return NONE;
//	}
//	
	
	
		Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE dorminf SET nuOfPp=?,dormTelephone=?  WHERE maxNuOfPp = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setInt(3,Integer.parseInt(maxNuOfPp));
		ps.setInt(1, Integer.parseInt(nuOfPp));
//		ps.setString(3, sex);
	//	ps.setInt(4, Integer.parseInt(id));
//		ps.setString(5, department);
//		ps.setString(6, major);  
//		ps.setString(7, dormNu);
		ps.setString(2, dormTelephone);
		//ps.setString(9, dormBdName);  
	//	ps.setString(4, studentId);  
		//ps.setInt(10, Integer.parseInt(id));
		
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	/*public int getAmountOfRow() {
		return amountOfRow;
	}
	public void setAmountOfRow(int amountOfRow) {
		this.amountOfRow = amountOfRow;
	}
	
	/* ---------------------------
	 * 		删除修理数据 ver.12.8
	 * ---------------------------
	 * 
	 * 从前台ajax请求获得delId参数
	 * 此参数为修理记录的ID，通过此ID
	 * 用SQL语句删除数据库中的记录
	 */
	/*public String delDorm() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String uId = request.getParameter("delId");
		int id = 0;
		if(uId != null){
			id = Integer.parseInt(uId);
		}else{
			return NONE;
		}
		Connection conn = Connection_mysql.getConnection();
		String sql = "DELETE FROM dorminf WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res == 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	

	/* -----------------------------
	 * 	只读取一行指定水电费记录 ver.11.24
	 * -----------------------------
	 * 
	 */
	/*public String loadADorms() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String DormId = request.getParameter("editId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(DormId == null || DormId.isEmpty() || !patternNu.matcher(DormId).matches()){
			return NONE;
		}
		String sql = "SELECT id, dormTelephone,maxNuOfPp,nuOfPp,dormTelephone, dormBdName, dormNu FROM studormcms.dorminf WHERE id = ?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(DormId));
		ResultSet res = ps.executeQuery();
		while(res.next()){
			dorm u = new dorm();
			u.setId(res.getInt("id"));
			
			
			u.setDormNu(res.getString("DormNu"));
			u.setmaxNuOfPp(res.getString("maxNuOfPp"));
			u.setnuOfPp(res.getString("nuOfPp"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormTelephone(res.getString("dormTelephone"));
	
			tableRow.add(u);			
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	/* --------------------------------
	 * 		编辑水电费信息 ver.12.13
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	/*public String editDorms() throws Exception{
		
		//Calendar rightNow = Calendar.getInstance();
		//SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		//String submitDate = fmt.format(rightNow.getTime());
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		
		String DormNu = request.getParameter("DormNu");
		String DormBdName = request.getParameter("DormBdName");	
		String maxNuOfPp = request.getParameter("maxNuOfPp");
		String nuOfPp = request.getParameter("nuOfPp");
		String DormTelephone = request.getParameter("dormTelephone");

		String id = request.getParameter("uid");
        
		Pattern patternNu = Pattern.compile("\\d*");
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		/*if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		if(DormBdName == null || DormBdName.isEmpty() || !patternNu.matcher(DormBdName).matches()){
			return NONE;
		}
		if(DormNu == null || DormNu.isEmpty() || !patternNu.matcher(DormNu).matches()){
			return NONE;
		}
		if(maxNuOfPp == null || maxNuOfPp.isEmpty() || !patternNu.matcher(maxNuOfPp).matches()){
			return NONE;
		}
		if(nuOfPp == null || nuOfPp.isEmpty() || !patternNu.matcher(nuOfPp).matches()){
			return NONE;
		}
		if(DormTelephone == null || DormTelephone.isEmpty() || !patternNu.matcher(DormTelephone).matches()){
			return NONE;
		}*/


	
	
	/*	Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE dormsinf SET dormBdName=?,dormNu=?,dormTelephone=?,maxNuOfPp=?,nuOfPp=? WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setString(1, DormNu);
		ps.setString(2, DormBdName);
		ps.setString(3, maxNuOfPp);
		ps.setString(4, nuOfPp);
		ps.setString(5, DormTelephone);
		ps.setInt(7, Integer.parseInt(id));
		
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	
	/* --------------------------------
	 * 		添加水电费信息 ver.11.23
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	//public String addDorm() throws Exception{
		
		//Calendar rightNow = Calendar.getInstance();
		//SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		//String submitDate = fmt.format(rightNow.getTime());

		
		/*HttpServletRequest request = ServletActionContext.getRequest(); 
		
		
		String dormName = request.getParameter("dormName");
		String dormNu = request.getParameter("dormNu");	
		String maxNuOfPp = request.getParameter("maxNuOfPp");
		String nuOfPp = request.getParameter("nuOfPp");
		String dormtelephone = request.getParameter("dormtelephone");
		String ID = request.getParameter("ID");
		Pattern patternNu = Pattern.compile("\\d{3}");// 匹配3位整数
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		if(dormName == null ||dormName.isEmpty()){
			return NONE;
		}
		if(dormNu == null ||dormNu.isEmpty() || !patternNu.matcher(dormNu).matches()){
			return NONE;
		}
		if(maxNuOfPp == null ||maxNuOfPp.isEmpty()){
			return NONE;
		}
		if(nuOfPp == null ||nuOfPp.isEmpty()){
			return NONE;
		}
		
		if(dormtelephone == null || dormtelephone.isEmpty()){
			return NONE;
		}
		if(ID == null || ID.isEmpty()){
			return NONE;
		}


	
	

		Connection conn = Connection_mysql.getConnection();
		String sql = "INSERT INTO dormsinf (dormName,dormNu, maxNuOfPp,nuOfPp,dormtelephone,ID, ) VALUES (?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		
		ps.setString(1, dormName);
		ps.setString(2, dormNu);
		ps.setString(3, maxNuOfPp);
		ps.setString(4, nuOfPp);
		ps.setString(5, dormtelephone);
		ps.setString(6, ID);
		
	
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	/* -----------------------------
	 * 		修改是否已维修 ver.12.16
	 * -----------------------------
	 * 
	 */
	/*public String alterComment() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		String sql = "UPDATE dormsinf SET comment = ? WHERE id=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(2, Integer.parseInt(id));
		if(status.equals("离校")){
			ps.setInt(1, 1);
		}else if(status.equals("在校")){
			ps.setInt(1, 0);
		}else{
			return NONE;
		}
		
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res > 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	public String loadStuDorm() throws Exception
	{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		HttpSession session = request.getSession();
		String id = (String)session.getAttribute("id");
		String sign = request.getParameter("issignin");	
		int issign;
		if(id == null ||id.isEmpty()){		
			return NONE;
		}
		if(sign.equals("离校"))
		{
			issign = 0;
		}
		else {
			issign=1;
		}
		String sql = "SELECT  id,  dormTelephone,maxNuOfPp,nuOfPp,dormTelephone, dormBdName, dormNu FROM studormcms.dorminf  WHERE = ? and =?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(id));
		ps.setInt(2,issign);
		ResultSet res = ps.executeQuery();
		while(res.next()){
			dorm u = new dorm();
			

	
			u.setDormNu(res.getString("DormNu"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setmaxNuOfPp(res.getString("maxNuPp"));
			u.setnuOfPp(res.getString("nuOfPp"));
			u.setDormTelephone(res.getString("DormTelephone"));
			
			tableRow.add(u);		
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}*/
}
